#!/usr/bin/perl -w
use strict;
use FindBin;
use Net::FTP;
use LWP::Simple;
use HTTP::Date;
use lib ("$FindBin::Bin/..", "/net/cpp-group/Leo/bin");
use dbh_helper;
use ortho_species;
my ($cmd);

print STDERR <<"HEADLINE";
888888888888888888888888888888888888888888888888888888888888888888888

    table_ens_id_update

	Description:
		Uses the cDNA sequence to fill in the pi field in the
			ens_id field
	Populates DB tables:
		ens_id

888888888888888888888888888888888888888888888888888888888888888888888

HEADLINE


#-----------------------------------------------------------------------------------------
#	Get DB parameters
#
use db_parameters;
#
#	Get DB parameters
#-----------------------------------------------------------------------------------------

my ($curr_dir) = $FindBin::Bin;
$curr_dir =~ s/^(.*)\/?$/$1/;

my $dbh = connect_to_panda();
use get_psql_time;
my $start_time = get_psql_time($dbh);



#
# populate ens tables with PIs
#
print "\tPopulate ens tables with PIs...\n";
$dbh->do("ANALYSE ens_id;");
$dbh->do(<<'PL/SQLCMD');
	SELECT
			c.pi,
			e.gene_id,
			e.prot_id,
			e.transcript_id,
            e.species
	INTO
		TEMP t_3
	FROM
		ens_id e LEFT JOIN
		cdna_sequences c USING (prot_id);
    TRUNCATE ens_id;
	INSERT
		INTO ens_id
		SELECT
			*
		FROM
			t_3;
PL/SQLCMD

#	UPDATE ens_id
#		SET pi = cdna_sequences.pi
#		FROM cdna_sequences
#		WHERE ens_id.prot_id = cdna_sequences.prot_id;


#
# Remove unmatched Ensembl IDs
#
my $unmatched_ens_id = dbh_get_single_value($dbh, "SELECT count (*) FROM ens_id WHERE pi IS NULL;");
if ($unmatched_ens_id)
{
	print "\t", "!" x 62, "\n",
			"\tError:\n\t\t",
			"$unmatched_ens_id entries from ens_id were not found in \n\t\t",
			"the peptide / cDNA fasta file\n\t\t",
			"This is mostly likely an ensembl thing.\n\t\t",
			"Have you failed to exclude pseudo genes or EST genes?\n",
			 "\t", "!" x 62, "\n";

	my $array = $dbh->selectall_arrayref(<<'PL/SQLCMD');
	SELECT
		gene_id,
		prot_id,
		transcript_id
	FROM
		ens_id
	WHERE
		pi IS NULL;
PL/SQLCMD
	{
		open (ERR, ">$dir_pipeline_errors/table_ens_id.identifiers_not_found_among_sequences.errors") or
			die "Error\n\tCould not open pi_is_null.errors";
		ERR->autoflush();
		for my $line(@$array)
		{
			print ERR join ("\t", @$line), "\n";
		}
	}

	$dbh->do("DELETE FROM ens_id WHERE pi IS NULL;");
}

$dbh->do("VACUUM ANALYSE ens_id;");
#_________________________________________________________________________________________

#	Cleanup and finish

#_________________________________________________________________________________________
#remove zero size (empty error/status) files
(-z $_) && unlink $_ for (glob("$dir_pipeline_errors/*"));
#print STDERR "\n";
log_pipeline_stage($dbh, 'NULL', 22, 'Update ens_id with pi from cdna_sequences', $start_time);
print STDERR "\tFinished.\n";
$dbh->disconnect();
